﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data.SQLite;

namespace Json2sdb
{
    /// <summary>
    /// 将任意结构的 JSON 或 .NET 对象转换为 .sdb 格式的 SQLite 数据库文件
    /// 静态工具类，自动扁平化嵌套结构，生成通用表
    /// </summary>
    public static class DynamicSdbConverter
    {
        /// <summary>
        /// 将任意 JSON 转换为 .sdb 文件
        /// </summary>
        /// <param name="json">JSON 字符串</param>
        /// <param name="dbFile">输出的 .sdb 文件路径</param>
        public static void Convert(string json, string dbFile)
        {
            if (string.IsNullOrWhiteSpace(json))
                throw new ArgumentException("JSON 数据不能为空");

            if (string.IsNullOrWhiteSpace(dbFile))
                throw new ArgumentException("数据库文件路径无效");

            JToken token = JToken.Parse(json);

            var flatRows = new List<Dictionary<string, object>>();
            ExtractRows(token, flatRows);

            if (flatRows.Count == 0)
                throw new InvalidOperationException("JSON 中未找到有效对象");

            CreateDatabase(dbFile, flatRows);
        }

        /// <summary>
        /// 从 JSON 文件加载数据并转换为 .sdb
        /// </summary>
        /// <param name="jsonFilePath">JSON 文件路径</param>
        /// <param name="dbFile">输出的 .sdb 文件路径</param>
        public static void ConvertFromFile(string jsonFilePath, string dbFile)
        {
            if (!File.Exists(jsonFilePath))
                throw new FileNotFoundException($"JSON 文件未找到：{jsonFilePath}");

            string json = File.ReadAllText(jsonFilePath, System.Text.Encoding.UTF8);
            Convert(json, dbFile);
        }

        /// <summary>
        /// 将任意 .NET 对象（支持匿名对象、POCO、集合等）转换为 .sdb 文件
        /// </summary>
        /// <param name="obj">任意 C# 对象</param>
        /// <param name="dbFile">输出的 .sdb 文件路径</param>
        public static void ConvertFromObject(object obj, string dbFile)
        {
            if (obj == null)
                throw new ArgumentNullException(nameof(obj), "输入对象不能为 null");

            if (string.IsNullOrWhiteSpace(dbFile))
                throw new ArgumentException("数据库文件路径无效");

            string json = JsonConvert.SerializeObject(obj);
            JToken token = JToken.Parse(json);

            var flatRows = new List<Dictionary<string, object>>();
            ExtractRows(token, flatRows);

            if (flatRows.Count == 0)
                throw new InvalidOperationException("对象序列化后未生成有效数据");

            CreateDatabase(dbFile, flatRows);
        }

        /// <summary>
        /// 递归提取所有对象并扁平化
        /// </summary>
        private static void ExtractRows(JToken token, List<Dictionary<string, object>> rows)
        {
            switch (token.Type)
            {
                case JTokenType.Array:
                    foreach (var item in token.Children())
                    {
                        if (item.Type == JTokenType.Object)
                        {
                            var flat = FlattenJson(item);
                            rows.Add(flat);
                        }
                    }
                    break;

                case JTokenType.Object:
                    var flatObj = FlattenJson(token);
                    rows.Add(flatObj);
                    break;

                default:
                    throw new InvalidOperationException("JSON 必须是对象或数组");
            }
        }

        /// <summary>
        /// 扁平化嵌套 JSON 对象，使用点号分隔键名
        /// </summary>
        private static Dictionary<string, object> FlattenJson(JToken token, string prefix = "")
        {
            var result = new Dictionary<string, object>();

            switch (token.Type)
            {
                case JTokenType.Object:
                    foreach (JProperty prop in token.Children<JProperty>())
                    {
                        string newPrefix = string.IsNullOrEmpty(prefix) ? prop.Name : prefix + "." + prop.Name;
                        var childDict = FlattenJson(prop.Value, newPrefix);
                        foreach (var kv in childDict)
                        {
                            result[kv.Key] = kv.Value;
                        }
                    }
                    break;

                case JTokenType.Array:
                    result[prefix] = token.ToString(Formatting.None);
                    break;

                case JTokenType.Null:
                case JTokenType.Undefined:
                    result[prefix] = null;
                    break;

                default:
                    result[prefix] = token.Value<object>();
                    break;
            }

            return result;
        }

        /// <summary>
        /// 创建 SQLite 数据库并插入扁平化数据
        /// </summary>
        private static void CreateDatabase(string dbFile, List<Dictionary<string, object>> rows)
        {
            if (File.Exists(dbFile))
                File.Delete(dbFile);

            var allColumns = new HashSet<string>();
            foreach (var row in rows)
            {
                foreach (var key in row.Keys)
                {
                    allColumns.Add(key);
                }
            }

            using (var connection = new SQLiteConnection($"Data Source={dbFile};Version=3;"))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var columnsDef = allColumns.Select(col => $"{EscapeColumnName(col)} TEXT");
                    string createTableSql = $"CREATE TABLE Data ({string.Join(", ", columnsDef)});";

                    using (var cmd = new SQLiteCommand(createTableSql, connection, transaction))
                    {
                        cmd.ExecuteNonQuery();
                    }

                    foreach (var row in rows)
                    {
                        var cols = new List<string>();
                        var placeholders = new List<string>();
                        var parameters = new List<SQLiteParameter>();

                        foreach (string col in allColumns)
                        {
                            cols.Add(EscapeColumnName(col));
                            placeholders.Add($"@{EscapeParamName(col)}");

                            object value = row.TryGetValue(col, out var val) ? val : null;
                            parameters.Add(new SQLiteParameter($"@{EscapeParamName(col)}", value ?? DBNull.Value));
                        }

                        string insertSql = $"INSERT INTO Data ({string.Join(", ", cols)}) VALUES ({string.Join(", ", placeholders)});";
                        using (var cmd = new SQLiteCommand(insertSql, connection, transaction))
                        {
                            cmd.Parameters.AddRange(parameters.ToArray());
                            cmd.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
            }
        }

        /// <summary>
        /// 转义列名（防止特殊字符）
        /// </summary>
        private static string EscapeColumnName(string name)
        {
            return $"[{name.Replace(".", "_")}]";
        }

        /// <summary>
        /// 转义参数名
        /// </summary>
        private static string EscapeParamName(string name)
        {
            return "p_" + name.Replace(".", "_").Replace("-", "_");
        }
    }
}